#Include "Protheus.ch"

User Function FatRegiao()

Local oReport := Nil

oReport := ReportDef()
oReport:PrintDialog()

Return Nil

// -------------------------------------------------------------------------------------- //

Static Function LoadData()

Local cAlias    := GetNextAlias()
Local cQuery    := RetQuery()
Local nRecCount := 0

Return Nil

// -------------------------------------------------------------------------------------- //

Static Function RetQuery()

Local cQuery := ""

cQuery += " SELECT D2_EST, D2_COD, B1_DESC, QUANT, D2_QUANT, QUANT * B1_PESO AS PESLIQ," + CRLF
cQuery += 	" QUANT * B1_PESBRU AS PESBRU" + CRLF
cQuery += " FROM (" + CRLF
cQuery += 	" SELECT D2_EST, D2_COD, B1_DESC, D2_QUANT, B1_PESO, B1_PESBRU," + CRLF
cQuery += 		" CASE" + CRLF
cQuery += 			" WHEN B1_TIPCONV = 'D'" + CRLF
cQuery += 				" THEN D2_QUANT / B1_CONV" + CRLF
cQuery += 			" WHEN B1_TIPCONV = 'M'" + CRLF
cQuery += 				" THEN D2_QUANT * B1_CONV" + CRLF
cQuery += 		" END AS QUANT" + CRLF
cQuery += 	" FROM (" + CRLF
cQuery += 		" SELECT D2_EST, D2_COD, B1_DESC, SUM(D2_QUANT) AS D2_QUANT," + CRLF
cQuery += 			" B1_CONV, B1_TIPCONV, B1_PESO, B1_PESBRU" + CRLF
cQuery += 		" FROM " + RetSql("SD2") + CRLF
cQuery += 			" INNER JOIN " + RetSql("SB1") + CRLF
cQuery += 				" ON B1_COD = D2_COD" + CRLF
cQuery += 		" WHERE D2_EMISSAO BETWEEN '" + DToS(MV_PAR01) + "' AND '" + DToS(MV_PAR02) + "'" + CRLF
cQuery += 			" AND D2_TP = 'PA'" + CRLF
cQuery += 			" AND (D2_GRUPO IN ('1', '2', '4') OR D2_COD IN('315500', '310000'))" + CRLF
cQuery += 			" AND " + RetSqlFil("SD2,SB1") + CRLF
cQuery += 			" AND " + RetSqlDel("SD2,SB1") + CRLF
cQuery += 		" GROUP BY D2_EST, D2_COD, B1_DESC, B1_CONV, B1_TIPCONV, B1_PESO, B1_PESBRU" + CRLF
cQuery += 	" ) AGRUP" + CRLF
cQuery += " ) QUANT" + CRLF
cQuery += " UNION" + CRLF
cQuery += " SELECT D2_EST, D2_COD, B1_DESC, SUM(C6_UNSVEN) AS C6_UNSVEN," + CRLF
cQuery += 	" SUM(C6_QTDPCT) AS C6_QTDPCT, SUM(D2_QUANT) AS D2_QUANT," + CRLF
cQuery += 	" SUM(C6_QTDBRU) AS C6_QTDBRU" + CRLF
cQuery += " FROM (" + CRLF
cQuery += 	" SELECT D2_EST, D2_COD, SUM(D2_QUANT) AS D2_QUANT, D2_PEDIDO, D2_ITEMPV" + CRLF
cQuery += 	" FROM " + RetSqlTab("SD2") + CRLF
cQuery += 	" WHERE D2_EMISSAO BETWEEN '" + DToS(MV_PAR01) + "' AND '" + DToS(MV_PAR02) + "'" + CRLF
cQuery += 		" AND D2_TP = 'PA'" + CRLF
cQuery += 		" AND (D2_GRUPO = '3' AND D2_COD NOT IN ('315500', '310000'))" + CRLF
cQuery += 		" AND " + RetSqlFil("SD2") + CRLF
cQuery += 		" AND " + RetSqlDel("SD2") + CRLF
cQuery += 	" GROUP BY D2_EST, D2_COD, D2_PEDIDO, D2_ITEMPV" + CRLF
cQuery += " ) AGRUP" + CRLF
cQuery += 	" INNER JOIN " + RetSqlTab("SC6") + CRLF
cQuery += 		" ON D2_PEDIDO = C6_NUM" + CRLF
cQuery += 			" AND C6_ITEM = D2_ITEMPV" + CRLF
cQuery += 	" INNER JOIN " + RetSqlTab("SB1") + CRLF
cQuery += 		" ON B1_COD = D2_COD" + CRLF
cQuery += " WHERE C6_FILIAL = '01'" + CRLF
cQuery += 	" AND " + RetSqlDel("SC6,SB1") + CRLF
cQuery += 	" AND " + RetSqlFil("SC6,SB1") + CRLF
cQuery += " GROUP BY D2_EST, D2_COD, B1_DESC" + CRLF
cQuery += " ORDER BY D2_EST, D2_COD"

Return cQuery